<?php
require_once('databaseLoginData.class.php');

class Database
{
	const TABLE_PREFIX = "ws_";
	const NO_RIGHTS = "Sie haben keine Berechtigung für diese Aktion";

	private $mysqli;
	private $currentUserID;
	private $errorMsg;

	public function __construct()
	{
		$errorMsg = "";

		// Userid setzen
		if (isset($_SESSION['userid']))
		{
			$this->currentUserID = $_SESSION['userid'];
		}
		else
		{
			$this->currentUserID = 0;
		}
			
		$this->mysqli = new mysqli(DatabaseLoginData::dbServer, DatabaseLoginData::dbUser,
				DatabaseLoginData::dbPassword, DatabaseLoginData::dbName);
	}

	public function setUserID($userID)
	{
		$this->currentUserID = $userID;
	}

	public function getDistrictsAnchors($districtID = null)
	{
		$query = "SELECT da.districtID, da.latitude, da.longitude, h.color "
		. "FROM " . self::TABLE_PREFIX . "districts_anchors da JOIN "
		.self::TABLE_PREFIX."districts d ON (da.districtID = d.id) "
		. "JOIN ".self::TABLE_PREFIX."hunters h ON (d.hunterID = h.id) ";

		if ($districtID != null)
		{
			$query .= "WHERE da.districtID = $districtID ";
		}

		$query .= "ORDER BY da.districtID, da.indexNumber";

		return $this->mysqli->query($query);
	}

	public function deleteDistrictAnchors($id, $displayMsg = true)
	{
		if (($this->checkDistrictBelongsToUser($id) && $this->checkUserRight("editOwnDistrict")) ||
				$this->checkUserRight("editDistrict"))
		{
			$query = "DELETE "
			. "FROM ".self::TABLE_PREFIX."districts_anchors "
			. "WHERE districtID = $id";

			$retval = $this->mysqli->query($query);

			if ($displayMsg)
			{
				$this->showMsg($retval, "DELETE");
			}

			return $retval;
		}
		else
		{
			$this->errorMsg = self::NO_RIGHTS;
			return false;
		}
	}

	public function insertDistrictAnchor($id, $index, $lat, $lng)
	{
		if (($this->checkDistrictBelongsToUser($id) && $this->checkUserRight("editOwnDistrict")) ||
				$this->checkUserRight("editDistrict"))
		{
			return $this->mysqli->query("INSERT INTO "
					.self::TABLE_PREFIX."districts_anchors "
					. "(districtID, indexNumber, latitude, longitude) "
					. "VALUES ($id, $index, $lat, $lng)");
		}
		else
		{
			$this->errorMsg = self::NO_RIGHTS;
			return false;
		}
	}

	public function getDistrictsByHunter($hunterid)
	{
		return $this->mysqli->query("SELECT id, name "
				. "FROM ".self::TABLE_PREFIX."districts "
				. "WHERE hunterID = $hunterid "
				. "ORDER BY name");
	}

	public function alterDistrictName($id, $name)
	{
		if (($this->checkDistrictBelongsToUser($id) && $this->checkUserRight("editOwnDistrict")) ||
				$this->checkUserRight("editDistrict"))
		{
			$query = "UPDATE ".self::TABLE_PREFIX."districts "
			. "SET name = '$name' "
			. "WHERE id = $id";

			return $this->mysqli->query($query);
		}
		else
		{
			$this->errorMsg = self::NO_RIGHTS;
			return false;
		}
			
	}

	public function insertDistrict($hunterid, $name)
	{
		if (($this->currentUserID == $hunterid && $this->checkUserRight("createOwnDistrict")) ||
				$this->checkUserRight("createDistrict"))
		{
			$query = "INSERT INTO ".self::TABLE_PREFIX."districts (hunterID, name) "
			. "VALUES ($hunterid, '$name')";

			return $this->mysqli->query($query);
		}
		else
		{
			$this->errorMsg = self::NO_RIGHTS;
			return false;
		}
	}

	public function deleteDistrict($id)
	{
		if (($this->checkDistrictBelongsToUser($id) && $this->checkUserRight("editOwnDistrict")) ||
				$this->checkUserRight("editDistrict"))
		{
			// Erst alle Whereabouts löschen
			$retval = $this->deleteWhereabouts($id, false);

			if ($retval == true)
			{
				// Erst alle Ankerpunkte löschen
				$retval = $this->deleteDistrictAnchors($id, false);
					
				if ($retval == true)
				{
					$query = "DELETE "
					. "FROM ".self::TABLE_PREFIX."districts "
					. "WHERE id = $id";

					$retval = $this->mysqli->query($query);
				}
			}

			return $retval;
		}
		else
		{
			$this->errorMsg = self::NO_RIGHTS;
			return false;
		}
	}

	public function getHunters()
	{
		return $this->mysqli->query("SELECT h.id, h.name, h.firstname, h.color, "
				. "h.nickname, g.name AS groupname "
				. "FROM ".self::TABLE_PREFIX."hunters h JOIN "
				.self::TABLE_PREFIX."groups g ON (h.groupID = g.id) "
				. "WHERE h.nickname <> 'admin' "
				. "ORDER BY h.name, h.firstname");
	}

	public function getHunter($id)
	{
		return $this->mysqli->query("SELECT h.id, h.name, h.firstname, h.color, "
				. "h.nickname, h.groupid "
				. "FROM ".self::TABLE_PREFIX."hunters h "
				. "WHERE h.id = $id");
	}

	public function insertHunter()
	{
		if ($this->checkUserRight("createHunter"))
		{
			$query = "INSERT INTO ".self::TABLE_PREFIX."hunters (nickname) "
			. "SELECT "
			. "CONCAT('user',CAST(MAX(id)+1 AS CHAR)) FROM "
			.self::TABLE_PREFIX."hunters";

			$retval = $this->mysqli->query($query);
			$this->showMsg($retval, "INSERT");
		}
		else
		{
			$this->showMsg(false, "");
		}
	}

	public function deleteHunter($id)
	{
		if ($id != $this->currentUserID && $this->checkUserRight("editHunter"))
		{
			// Erst alle Whereabouts löschen
			$query = "DELETE w "
			. "FROM ".self::TABLE_PREFIX."whereabouts w "
			. "JOIN ".self::TABLE_PREFIX."districts d ON (w.districtID = d.id) "
			. "WHERE d.hunterID = $id ";

			$retval = $this->mysqli->query($query);

			if ($retval)
			{
				// Erst alle Ankerpunkte löschen
				$query = "DELETE da "
				. "FROM ".self::TABLE_PREFIX."districts_anchors da "
				. "JOIN ".self::TABLE_PREFIX."districts d ON (da.districtID = d.id) "
				. "WHERE d.hunterID = $id ";

				$retval = $this->mysqli->query($query);

				if ($retval)
				{
					// Erst alle Reviere löschen
					$query = "DELETE "
					. "FROM ".self::TABLE_PREFIX."districts "
					. "WHERE hunterID = $id";

					$retval = $this->mysqli->query($query);

					if ($retval)
					{
						$query = "DELETE "
						. "FROM ".self::TABLE_PREFIX."hunters "
						. "WHERE id = $id";

						$retval = $this->mysqli->query($query);
					}
				}
			}

			$this->showMsg($retval, "DELETE");

			return $retval;
		}
		else
		{
			$this->showMsg(false, "");
			return false;
		}
	}

	private function ageWhereabouts()
	{
		// Settings überprüfen, wann das letzte mal geprüft/gealtert wurde
		if ($this->isTimeToAge())
		{
			// Whereabouts altern lassen
			$query = "UPDATE ".self::TABLE_PREFIX."whereabouts w JOIN "
			.self::TABLE_PREFIX."whereabout_types wt ON ( w.typeID = wt.id ) "
			."SET w.typeID = wt.agingToTypeId "
			."WHERE wt.agingPeriod > 0 "
			."AND wt.agingPeriod < TIMESTAMPDIFF(SECOND , w.timestamp,CURRENT_TIMESTAMP)";

			$this->mysqli->query($query);

			// Zeit der letzten Prüfung merken
			$this->setLastAgingTime();
		}
	}

	public function getWhereabouts($districtID = null, $typeID = null)
	{
		// Whereabouts altern lassen
		$this->ageWhereabouts();

		// Whereabouts liefern
		$query = "SELECT w.id, w.latitude, w.longitude, w.radius, w.districtID, "
		. "a.color, DATE_FORMAT(w.timestamp,'%d.%m.%Y %H:%i:%s') AS timestamp, "
		. "t.icon, t.id AS typeID, h.name AS hunterName, h.firstname AS hunterFirstname, "
		. "w.timestamp AS compareTime "
		. "FROM ".self::TABLE_PREFIX."whereabouts w "
		. "JOIN ".self::TABLE_PREFIX."animals a ON (w.animalID = a.id) "
		. "JOIN ".self::TABLE_PREFIX."whereabout_types t ON (w.typeID = t.id) "
		. "JOIN ".self::TABLE_PREFIX."districts d ON (w.districtID = d.id) "
		. "JOIN ".self::TABLE_PREFIX."hunters h ON (d.hunterID = h.id) ";
			
		if ($districtID != null && $typeID != null) {
			$query .= "WHERE districtID = $districtID AND typeID = $typeID ";
		} else if ($districtID != null) {
			$query .= "WHERE districtID = $districtID ";
		} else if ($typeID != null) {
			$query .= "WHERE typeID = $typeID ";
		}
			
		$query .= "ORDER BY w.timestamp";

		return $this->mysqli->query($query);
	}

	public function getWhereaboutTimestamps()
	{
		$query = "SELECT DISTINCT DATE_FORMAT(w.timestamp,'%d.%m.%Y %H:%i:%s') AS timestamp, "
		. "w.timestamp AS compareTime "
		. "FROM ".self::TABLE_PREFIX."whereabouts w "
		. "ORDER BY w.timestamp";

		return $this->mysqli->query($query);
	}

	public function getWhereaboutTypes()
	{
		$query = "SELECT id, name, icon FROM ".self::TABLE_PREFIX."whereabout_types "
		. "ORDER BY name";
			
		return $this->mysqli->query($query);
	}

	public function getWhereaboutIcon($id)
	{
		$query = "SELECT icon FROM ".self::TABLE_PREFIX."whereabout_types WHERE id = $id";
			
		$res = $this->mysqli->query($query);
		if ($row = $res->fetch_assoc())
		{
			return $row['icon'];
		}
		else
		{
			return "";
		}
	}

	public function deleteWhereabouts($districtID, $displayMsg = true)
	{
		if (($this->checkDistrictBelongsToUser($districtID) && $this->checkUserRight("editOwnWhereabouts")) ||
				$this->checkUserRight("editWhereabouts"))
		{
			$query = "DELETE "
			. "FROM ".self::TABLE_PREFIX."whereabouts "
			. "WHERE districtID = $districtID";

			$retval = $this->mysqli->query($query);

			if ($displayMsg)
			{
				$this->showMsg($retval, "DELETE");
			}

			return $retval;
		}
		else
		{
			if ($displayMsg)
			{
				$this->showMsg(false, "");
			}
			return false;
		}
	}

	public function deleteWhereabout($id)
	{
		if (($this->checkWhereaboutBelongsToUser($id) && $this->checkUserRight("editOwnWhereabouts")) ||
				$this->checkUserRight("editWhereabouts"))
		{
			$query = "DELETE "
			. "FROM ".self::TABLE_PREFIX."whereabouts "
			. "WHERE id = $id";

			return $this->mysqli->query($query);
		}
		else
		{
			$this->errorMsg = self::NO_RIGHTS;
			return false;
		}
	}

	public function insertWhereabout($districtID, $lat, $lng, $radius, $animalID, $typeID)
	{
		if (($this->checkDistrictBelongsToUser($districtID) && $this->checkUserRight("editOwnWhereabouts")) ||
				$this->checkUserRight("editWhereabouts"))
		{
			return $this->mysqli->query("INSERT INTO ".self::TABLE_PREFIX."whereabouts "
					. "(districtID, latitude, longitude, radius, animalID, typeID) "
					. "VALUES ($districtID, $lat, $lng, $radius, $animalID, $typeID)");
		}
		else
		{
			$this->errorMsg = self::NO_RIGHTS;
			return false;
		}
	}

	public function alterHunter($id, $name, $firstname, $color, $groupid, $nickname, $password)
	{
		$query = "SELECT groupID FROM ".self::TABLE_PREFIX."hunters WHERE id = $id";
		$res = $this->mysqli->query($query);
			
		if ($row = $res->fetch_assoc())
		{
			$currentGroupID = $row['groupID'];
		}
		else
		{
			$this->showMsg(false, "UPDATE");
			return false;
		}
			
		$allowed = false;
		if (($currentGroupID == $groupid) &&
				(($id == $this->currentUserID && $this->checkUserRight("editOwnHunter")) ||
						$this->checkUserRight("editHunter")))
		{
			$allowed = true;
		}
		else if (($currentGroupID != $groupid) &&
				$this->checkUserRight("editHunter") &&
				$this->checkUserRight("createHunter"))
		{
			$allowed = true;
		}

		if ($allowed == true)
		{
			$query = "UPDATE ".self::TABLE_PREFIX."hunters "
			. "SET name = '$name', firstname = '$firstname', color = '$color', "
			. "nickname = '$nickname', groupID = $groupid ";
			if ($password != "")
			{
				$query .= ", password = SHA1('$password') ";
			}

			$query .= "WHERE id = $id";

			$retval = $this->mysqli->query($query);
			$this->showMsg($retval, "UPDATE");
			return $retval;
		}
		else
		{
			$this->showMsg(false, "");
			return false;
		}
	}
	
	public function getHuntersAndDistricts() {
		$query = "SELECT d.id, d.name AS districtname, h.name AS huntername, h.firstname "
			."FROM ".self::TABLE_PREFIX."districts d "
			."JOIN ".self::TABLE_PREFIX."hunters h ON (d.hunterid = h.id) "
			."ORDER BY h.name, h.firstname, d.name";
		return $this->mysqli->query($query);
	}

	public function getGroups()
	{
		$query = "SELECT id, name FROM ".self::TABLE_PREFIX."groups WHERE id > 1 ORDER BY name";
		return $this->mysqli->query($query);
	}

	private function checkDistrictBelongsToUser($districtID)
	{
		$query = "SELECT hunterID "
		."FROM ".self::TABLE_PREFIX."districts "
		."WHERE id = $districtID";
			
		$res = $this->mysqli->query($query);
		if (($row = $res->fetch_assoc()) && ($row['hunterID'] == $this->currentUserID))
		{
			return true;
		}

		return false;
	}

	private function checkWhereaboutBelongsToUser($id)
	{
		$query = "SELECT d.hunterID "
		."FROM ".self::TABLE_PREFIX."districts d JOIN "
		.self::TABLE_PREFIX."whereabouts w ON (w.districtID = d.id) "
		."WHERE w.id = $id";
			
		$res = $this->mysqli->query($query);
		if (($row = $res->fetch_assoc()) && ($row['hunterID'] == $this->currentUserID))
		{
			return true;
		}

		return false;
	}

	private function checkUserRight($right)
	{
		if ($this->currentUserID > 0)
		{
			$query = "SELECT g.$right "
			."FROM ".self::TABLE_PREFIX."groups g JOIN "
			.self::TABLE_PREFIX."hunters h ON (g.id = h.groupID) "
			."WHERE h.id = $this->currentUserID";

			$res = $this->mysqli->query($query);
			if (($row = $res->fetch_assoc()) && ($row[$right] == 1))
			{
				return true;
			}
		}
			
		return false;
	}

	public static function showMsg($success, $operation)
	{
		if ($success == true)
		{
			$class = "successMsg";
			$msg = " erfolgreich";
		}
		else
		{
			$class = "failMsg";
			$msg = " fehlgeschlagen";
		}
			
		if ($operation == "")
		{
			$msg = self::NO_RIGHTS;
		}
			
		echo '<div id="dbMsg" class="' .$class. '">' . "\n"
		. $operation . $msg
		. "</div>\n";
	}

	private function isTimeToAge()
	{
		$res = $this->mysqli->query(
				"SELECT lastAging, agingCheckInterval FROM "
				.self::TABLE_PREFIX."settings");
			
		if ($row = $res->fetch_assoc())
		{
			return $row['lastAging'] + $row['agingCheckInterval'] < time();
		}
			
		return false;
	}

	private function setLastAgingTime()
	{
		return $this->mysqli->query("UPDATE "
				.self::TABLE_PREFIX."settings SET lastAging = " . time());
	}

	public function getMapSettings()
	{
		if ($this->currentUserID > 0)
		{
			// Userspezifische Einstellungen
			$tablename = self::TABLE_PREFIX."hunters WHERE id = $this->currentUserID";
		}
		else
		{
			// Default Einstellungen
			$tablename = self::TABLE_PREFIX."settings";
		}
			
		return $this->mysqli->query("SELECT mapZoom, mapCenterLat, mapCenterLng, mapDisplayType "
				. "FROM $tablename");
	}

	public function setUserSetting($key, $value, $isString)
	{
		if ($this->currentUserID > 0)
		{
			$query = "UPDATE ".self::TABLE_PREFIX."hunters SET $key = ";
			$query .= $isString ? "'$value'" : "$value";
			$query .= " WHERE id = $this->currentUserID";
			return $this->mysqli->query($query);
		}
			
		return false;
	}

	public function checkLogin($username, $password)
	{
		$res = $this->mysqli->query("SELECT id "
				. "FROM ".self::TABLE_PREFIX."hunters "
				. "WHERE nickname='$username' AND password=SHA1('$password')");

		$retval = 0;

		if ($row = $res->fetch_assoc())
		{
			$retval = $row['id'];
		}
			
		$this->showMsg($retval > 0, "LOGIN");
			
		return $retval;
	}

	public function getHunterNickname($id)
	{
		$res = $this->mysqli->query("SELECT nickname FROM "
				.self::TABLE_PREFIX."hunters WHERE id = $id");
			
		if ($row = $res->fetch_assoc())
		{
			return $row['nickname'];
		}
			
		return "";
	}

	public function getPhonelists()
	{
		return $this->mysqli->query("SELECT id, name FROM "
				.self::TABLE_PREFIX."phonelists ORDER BY name");
	}

	public function getPhonelist($id)
	{
		return $this->mysqli->query("SELECT id, name, firstname, phone, mobile "
				."FROM ".self::TABLE_PREFIX."phonelist_entries WHERE listID = $id "
				."ORDER BY name, firstname");
	}

	public function deletePhonelistEntry($id)
	{
		if ($this->checkUserRight("editPhonelistEntries"))
		{
			$query = "DELETE "
			. "FROM ".self::TABLE_PREFIX."phonelist_entries "
			. "WHERE id = $id";

			$retval = $this->mysqli->query($query);

			$this->showMsg($retval, "DELETE");
			return $retval;
		}
		else
		{
			$this->showMsg(false, "");
			return false;
		}
	}

	public function insertPhonelistEntry($listid)
	{
		if ($this->checkUserRight("editPhonelistEntries"))
		{
			$query = "INSERT INTO ".self::TABLE_PREFIX."phonelist_entries (listID) "
			. "VALUES($listid)";

			$retval = $this->mysqli->query($query);

			$this->showMsg($retval, "INSERT");
			return $retval;
		}
		else
		{
			$this->showMsg(false, "");
			return false;
		}
	}

	public function getPhonelistEntry($id)
	{
		$query = "SELECT pe.name, pe.firstname, pe.phone, pe.mobile, pe.listID "
		. "FROM ".self::TABLE_PREFIX."phonelist_entries pe "
		. "WHERE id = $id";
		return $this->mysqli->query($query);
	}

	public function alterPhonelistEntry($id, $name, $firstname, $phone, $mobile, $listid)
	{
		if ($this->checkUserRight("editPhonelistEntries"))
		{
			$query = "UPDATE ".self::TABLE_PREFIX."phonelist_entries "
			. "SET name = '$name', firstname='$firstname', "
			. "phone='$phone', mobile='$mobile', listid='$listid'"
			. "WHERE id = $id";

			$retval = $this->mysqli->query($query);
			$this->showMsg($retval, "UPDATE");
		}
		else
		{
			$this->showMsg(false, "");
		}
			
	}
	
	public function getWhereaboutTypeSettings() {
		$query = "SELECT wt.id AS typeid, wt.name, wt.icon, wt.agingPeriod, wt.agingToTypeId "
			."FROM ".self::TABLE_PREFIX."whereabout_types wt "
			."ORDER BY wt.name";

		return $this->mysqli->query($query);
	}
	
	public function setWhereaboutTypeSettings($id, $name, $icon, $agingPeriod, $agingTo) {
		if ($this->checkUserRight("editSettings")) {
			$query = "UPDATE ".self::TABLE_PREFIX."whereabout_types wt "
				."SET wt.name= '$name', wt.icon='$icon', wt.agingPeriod=$agingPeriod, wt.agingToTypeId=$agingTo "
				."WHERE wt.id = $id";
			return $this->mysqli->query($query);
		} else {
			$this->showMsg(false, "");
			return false;
		}
	}

	public function jsDistrictAnchors($districtID = null)
	{
		if ($districtID != null)
		{
			$res = $this->getDistrictsAnchors($districtID);
		}
		else
		{
			$res = $this->getDistrictsAnchors();
		}

		$index = 0;
		$retval = "";

		while ($row = $res->fetch_assoc()) {
			$retval .= "districtsMap[" . $index . "] = {\n"
			."id: " . $row['districtID'] . ",\n"
			."color: \"" . $row['color'] . "\",\n"
			."anchor: new google.maps.LatLng(" . $row['latitude'] . "," . $row['longitude'] . ")\n"
			."};\n";

			$index++;
		}
			
		return $retval;
	}

	public function jsWhereabouts($districtID = null, $typeID = null)
	{
		$res = $this->getWhereabouts($districtID, $typeID);
			
		$retval = "";
			
		// Icon für Marker setzen
		if ($typeID != null) {
			$retval = "markerIcon='".$this->getWhereaboutIcon($typeID)."';\n";
		}

		while ($row = $res->fetch_assoc()) {
			$retval .= "whereaboutsMap[" . $row['id'] . "] = {\n"
			."center: new google.maps.LatLng(" . $row['latitude'] . "," . $row['longitude'] . "),\n"
			."radius: " . $row['radius'] . ",\n"
			."districtID: ".$row['districtID'].",\n"
			."color: \"".$row['color']."\",\n"
			."icon: \"".$row['icon']."\",\n"
			."typeid: \"".$row['typeID']."\",\n"
			."hunter: \"".$row['hunterName']." ".$row['hunterFirstname']."\",\n"
			."compareTime: \"".$row['compareTime']."\",\n"
			.'timestamp:"'.$row['timestamp'].'"'."\n"
			."};\n";
		}
			
		return $retval;
	}

	public function jsMapSettings()
	{
		$res = $this->getMapSettings();
		$retval = "";
			
		if ($row = $res->fetch_assoc())
		{
			$retval .= "var mapOptions = {\n"
			."center: new google.maps.LatLng(" . $row['mapCenterLat'] . "," . $row['mapCenterLng'] . "),\n"
			."zoom: " . $row['mapZoom'] . ",\n"
			."mapTypeId: google.maps.MapTypeId.".$row['mapDisplayType']."\n"
			."};\n";
		}
			
		return $retval;
	}

	public function jsDistrictList($hunterID) {
		$retval = "";

		if ($hunterID != null) {
			$res = $this->getDistrictsByHunter($hunterID);
			$index = 0;
				
			while ($row = $res->fetch_assoc()) {
				$retval .= "hunterDistricts[" . $index . "] = {\n"
				. 'name: "' . $row['name'] . '",'
				. "\nid: " . $row['id'] . "\n};\n";
				$index++;
			}
		}

		return $retval;
	}
	
	public function getErrorMessage() {
		return $this->errorMsg;
	}

	public static function printOptionLoop($min, $max, $fillCount, $selectLast)
	{
		$format = "%0".$fillCount."d";

		for ($i = $min; $i <= $max; $i++)
		{
			echo '<option';
			if ($selectLast && $i == $max)
			{
				echo ' selected="selected"';
			}
			echo '>'.sprintf($format,$i)."</option>\n";
		}
	}
}
?>